{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "import xmind\n",
    "from xmind.core.const import TOPIC_DETACHED\n",
    "from xmind.core.markerref import MarkerId\n",
    "from xmind.core.topic import TopicElement\n",
    "\n",
    "\n",
    "def gen_my_xmind_file():\n",
    "    # load an existing file or create a new workbook if nothing is found\n",
    "    workbook = xmind.load(\"my.xmind\")\n",
    "    # get the first sheet(a new workbook has a blank sheet by default)\n",
    "    sheet1 = workbook.getPrimarySheet()\n",
    "    design_sheet1(sheet1)\n",
    "    # create sheet2\n",
    "    gen_sheet2(workbook, sheet1)\n",
    "    # now we save as test.xmind\n",
    "    xmind.save(workbook, path='test.xmind')\n",
    "\n",
    "\n",
    "def design_sheet1(sheet1):\n",
    "    # ***** first sheet *****\n",
    "    sheet1.setTitle(\"first sheet\")  # set its title\n",
    "\n",
    "    # get the root topic of this sheet(a sheet has  a blank root topic by default)\n",
    "    root_topic1 = sheet1.getRootTopic()\n",
    "    root_topic1.setTitle(\"root node\")  # set its title\n",
    "\n",
    "    # create some sub topic element\n",
    "    sub_topic1 = root_topic1.addSubTopic()\n",
    "    sub_topic1.setTitle(\"first sub topic\")\n",
    "\n",
    "    sub_topic2 = root_topic1.addSubTopic()\n",
    "    sub_topic2.setTitle(\"second sub topic\")\n",
    "\n",
    "    sub_topic3 = root_topic1.addSubTopic()\n",
    "    sub_topic3.setTitle(\"third sub topic\")\n",
    "\n",
    "    sub_topic4 = root_topic1.addSubTopic()\n",
    "    sub_topic4.setTitle(\"fourth sub topic\")\n",
    "\n",
    "    # create a detached topic(attention: only root topic can add a detached topic)\n",
    "    detached_topic1 = root_topic1.addSubTopic(topics_type=TOPIC_DETACHED)\n",
    "    detached_topic1.setTitle(\"detached topic\")\n",
    "    detached_topic1.setPosition(0, 30)\n",
    "\n",
    "    sub_topic1_1 = sub_topic1.addSubTopic()\n",
    "    sub_topic1_1.setTitle(\"I'm a sub topic too\")\n",
    "\n",
    "\n",
    "def gen_sheet2(workbook, sheet1):\n",
    "    # ***** second sheet *****\n",
    "    # create a new sheet and add to the workbook by default\n",
    "    sheet2 = workbook.createSheet()\n",
    "    sheet2.setTitle(\"second sheet\")\n",
    "\n",
    "    # a sheet has a blank sheet by default\n",
    "    root_topic2 = sheet2.getRootTopic()\n",
    "    root_topic2.setTitle(\"root node\")\n",
    "\n",
    "    # use other methods to create some sub topic element\n",
    "    topic1 = TopicElement(ownerWorkbook=workbook)\n",
    "    # set a topic hyperlink from this topic to the first sheet given by s1.getID()\n",
    "    topic1.setTopicHyperlink(sheet1.getID())\n",
    "    topic1.setTitle(\"redirection to the first sheet\")  # set its title\n",
    "\n",
    "    topic2 = TopicElement(ownerWorkbook=workbook)\n",
    "    topic2.setTitle(\"topic with an url hyperlink\")\n",
    "    topic2.setURLHyperlink(\"https://github.com/zhuifengshen/xmind\")  # set an url hyperlink\n",
    "\n",
    "    topic3 = TopicElement(ownerWorkbook=workbook)\n",
    "    topic3.setTitle(\"third node\")\n",
    "    topic3.setPlainNotes(\"notes for this topic\")  # set notes (F4 in XMind)\n",
    "    topic3.setTitle(\"topic with \\n notes\")\n",
    "\n",
    "    topic4 = TopicElement(ownerWorkbook=workbook)\n",
    "    topic4.setFileHyperlink(\"logo.png\")  # set a file hyperlink\n",
    "    topic4.setTitle(\"topic with a file\")\n",
    "\n",
    "    topic1_1 = TopicElement(ownerWorkbook=workbook)\n",
    "    topic1_1.setTitle(\"sub topic\")\n",
    "    topic1_1.addLabel(\"a label\")  # official XMind only can a one label\n",
    "\n",
    "    topic1_1_1 = TopicElement(ownerWorkbook=workbook)\n",
    "    topic1_1_1.setTitle(\"topic can add multiple markers\")\n",
    "    topic1_1_1.addMarker(MarkerId.starBlue)\n",
    "    topic1_1_1.addMarker(MarkerId.flagGreen)\n",
    "\n",
    "    topic2_1 = TopicElement(ownerWorkbook=workbook)\n",
    "    topic2_1.setTitle(\"topic can add multiple comments\")\n",
    "    topic2_1.addComment(\"I'm a comment!\")\n",
    "    topic2_1.addComment(content=\"Hello comment!\", author='devin')\n",
    "\n",
    "    # then the topics must be added to the root element\n",
    "    root_topic2.addSubTopic(topic1)\n",
    "    root_topic2.addSubTopic(topic2)\n",
    "    root_topic2.addSubTopic(topic3)\n",
    "    root_topic2.addSubTopic(topic4)\n",
    "    topic1.addSubTopic(topic1_1)\n",
    "    topic2.addSubTopic(topic2_1)\n",
    "    topic1_1.addSubTopic(topic1_1_1)\n",
    "\n",
    "    # to loop on the subTopics\n",
    "    topics = root_topic2.getSubTopics()\n",
    "    for index, topic in enumerate(topics):\n",
    "        topic.addMarker(\"priority-\" + str(index + 1))\n",
    "\n",
    "    # create a relationship\n",
    "    sheet2.createRelationship(topic1.getID(), topic2.getID(), \"relationship test\")\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    gen_my_xmind_file()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\18027\\AppData\\Local\\Temp\\ipykernel_4412\\3205929556.py:10: FutureWarning: The provided callable <function sum at 0x000001C67F98F920> is currently using SeriesGroupBy.sum. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"sum\" instead.\n",
      "  df_group = df.groupby(['查询账号','进出标准','收款方的商户名称']).agg({'交易金额':[np.sum,np.size]}).reset_index()\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 279 entries, 0 to 278\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  279 non-null    object \n",
      " 1   进账        279 non-null    float64\n",
      " 2   进账次数      279 non-null    int64  \n",
      " 3   出账        279 non-null    float64\n",
      " 4   出账次数      279 non-null    int64  \n",
      " 5   查询账号      279 non-null    object \n",
      " 6   进出差       279 non-null    float64\n",
      " 7   进出差次数     279 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 17.6+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 465 entries, 0 to 464\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  465 non-null    object \n",
      " 1   进账        465 non-null    float64\n",
      " 2   进账次数      465 non-null    int64  \n",
      " 3   出账        465 non-null    float64\n",
      " 4   出账次数      465 non-null    int64  \n",
      " 5   查询账号      465 non-null    object \n",
      " 6   进出差       465 non-null    float64\n",
      " 7   进出差次数     465 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 29.2+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 326 entries, 0 to 325\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  326 non-null    object \n",
      " 1   进账        326 non-null    float64\n",
      " 2   进账次数      326 non-null    int64  \n",
      " 3   出账        326 non-null    float64\n",
      " 4   出账次数      326 non-null    int64  \n",
      " 5   查询账号      326 non-null    object \n",
      " 6   进出差       326 non-null    float64\n",
      " 7   进出差次数     326 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 20.5+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 247 entries, 0 to 246\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  247 non-null    object \n",
      " 1   进账        247 non-null    float64\n",
      " 2   进账次数      247 non-null    int64  \n",
      " 3   出账        247 non-null    float64\n",
      " 4   出账次数      247 non-null    int64  \n",
      " 5   查询账号      247 non-null    object \n",
      " 6   进出差       247 non-null    float64\n",
      " 7   进出差次数     247 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 15.6+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 123 entries, 0 to 122\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  123 non-null    object \n",
      " 1   进账        123 non-null    float64\n",
      " 2   进账次数      123 non-null    int64  \n",
      " 3   出账        123 non-null    float64\n",
      " 4   出账次数      123 non-null    int64  \n",
      " 5   查询账号      123 non-null    object \n",
      " 6   进出差       123 non-null    float64\n",
      " 7   进出差次数     123 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 7.8+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 299 entries, 0 to 298\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  299 non-null    object \n",
      " 1   进账        299 non-null    float64\n",
      " 2   进账次数      299 non-null    int64  \n",
      " 3   出账        299 non-null    float64\n",
      " 4   出账次数      299 non-null    int64  \n",
      " 5   查询账号      299 non-null    object \n",
      " 6   进出差       299 non-null    float64\n",
      " 7   进出差次数     299 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 18.8+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 26 entries, 0 to 25\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  26 non-null     object \n",
      " 1   进账        26 non-null     float64\n",
      " 2   进账次数      26 non-null     int64  \n",
      " 3   出账        26 non-null     float64\n",
      " 4   出账次数      26 non-null     int64  \n",
      " 5   查询账号      26 non-null     object \n",
      " 6   进出差       26 non-null     float64\n",
      " 7   进出差次数     26 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 1.8+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 426 entries, 0 to 425\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  426 non-null    object \n",
      " 1   进账        426 non-null    float64\n",
      " 2   进账次数      426 non-null    int64  \n",
      " 3   出账        426 non-null    float64\n",
      " 4   出账次数      426 non-null    int64  \n",
      " 5   查询账号      426 non-null    object \n",
      " 6   进出差       426 non-null    float64\n",
      " 7   进出差次数     426 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 26.8+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 382 entries, 0 to 381\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  382 non-null    object \n",
      " 1   进账        382 non-null    float64\n",
      " 2   进账次数      382 non-null    int64  \n",
      " 3   出账        382 non-null    float64\n",
      " 4   出账次数      382 non-null    int64  \n",
      " 5   查询账号      382 non-null    object \n",
      " 6   进出差       382 non-null    float64\n",
      " 7   进出差次数     382 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 24.0+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 302 entries, 0 to 301\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  302 non-null    object \n",
      " 1   进账        302 non-null    float64\n",
      " 2   进账次数      302 non-null    int64  \n",
      " 3   出账        302 non-null    float64\n",
      " 4   出账次数      302 non-null    int64  \n",
      " 5   查询账号      302 non-null    object \n",
      " 6   进出差       302 non-null    float64\n",
      " 7   进出差次数     302 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 19.0+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 366 entries, 0 to 365\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  366 non-null    object \n",
      " 1   进账        366 non-null    float64\n",
      " 2   进账次数      366 non-null    int64  \n",
      " 3   出账        366 non-null    float64\n",
      " 4   出账次数      366 non-null    int64  \n",
      " 5   查询账号      366 non-null    object \n",
      " 6   进出差       366 non-null    float64\n",
      " 7   进出差次数     366 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 23.0+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  1 non-null      object \n",
      " 1   进账        1 non-null      float64\n",
      " 2   进账次数      1 non-null      int64  \n",
      " 3   出账        1 non-null      float64\n",
      " 4   出账次数      1 non-null      int64  \n",
      " 5   查询账号      1 non-null      object \n",
      " 6   进出差       1 non-null      float64\n",
      " 7   进出差次数     1 non-null      int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 196.0+ bytes\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 19 entries, 0 to 18\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  19 non-null     object \n",
      " 1   进账        19 non-null     float64\n",
      " 2   进账次数      19 non-null     int64  \n",
      " 3   出账        19 non-null     float64\n",
      " 4   出账次数      19 non-null     int64  \n",
      " 5   查询账号      19 non-null     object \n",
      " 6   进出差       19 non-null     float64\n",
      " 7   进出差次数     19 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 1.3+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 23 entries, 0 to 22\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  23 non-null     object \n",
      " 1   进账        23 non-null     float64\n",
      " 2   进账次数      23 non-null     int64  \n",
      " 3   出账        23 non-null     float64\n",
      " 4   出账次数      23 non-null     int64  \n",
      " 5   查询账号      23 non-null     object \n",
      " 6   进出差       23 non-null     float64\n",
      " 7   进出差次数     23 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 1.6+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 276 entries, 0 to 275\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  276 non-null    object \n",
      " 1   进账        276 non-null    float64\n",
      " 2   进账次数      276 non-null    int64  \n",
      " 3   出账        276 non-null    float64\n",
      " 4   出账次数      276 non-null    int64  \n",
      " 5   查询账号      276 non-null    object \n",
      " 6   进出差       276 non-null    float64\n",
      " 7   进出差次数     276 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 17.4+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 33 entries, 0 to 32\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  33 non-null     object \n",
      " 1   进账        33 non-null     float64\n",
      " 2   进账次数      33 non-null     int64  \n",
      " 3   出账        33 non-null     float64\n",
      " 4   出账次数      33 non-null     int64  \n",
      " 5   查询账号      33 non-null     object \n",
      " 6   进出差       33 non-null     float64\n",
      " 7   进出差次数     33 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 2.2+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14 entries, 0 to 13\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  14 non-null     object \n",
      " 1   进账        14 non-null     float64\n",
      " 2   进账次数      14 non-null     int64  \n",
      " 3   出账        14 non-null     float64\n",
      " 4   出账次数      14 non-null     int64  \n",
      " 5   查询账号      14 non-null     object \n",
      " 6   进出差       14 non-null     float64\n",
      " 7   进出差次数     14 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 1.0+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 215 entries, 0 to 214\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  215 non-null    object \n",
      " 1   进账        215 non-null    float64\n",
      " 2   进账次数      215 non-null    int64  \n",
      " 3   出账        215 non-null    float64\n",
      " 4   出账次数      215 non-null    int64  \n",
      " 5   查询账号      215 non-null    object \n",
      " 6   进出差       215 non-null    float64\n",
      " 7   进出差次数     215 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 13.6+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 75 entries, 0 to 74\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  75 non-null     object \n",
      " 1   进账        75 non-null     float64\n",
      " 2   进账次数      75 non-null     int64  \n",
      " 3   出账        75 non-null     float64\n",
      " 4   出账次数      75 non-null     int64  \n",
      " 5   查询账号      75 non-null     object \n",
      " 6   进出差       75 non-null     float64\n",
      " 7   进出差次数     75 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 4.8+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  2 non-null      object \n",
      " 1   进账        2 non-null      float64\n",
      " 2   进账次数      2 non-null      int64  \n",
      " 3   出账        2 non-null      float64\n",
      " 4   出账次数      2 non-null      int64  \n",
      " 5   查询账号      2 non-null      object \n",
      " 6   进出差       2 non-null      float64\n",
      " 7   进出差次数     2 non-null      int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 260.0+ bytes\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 64 entries, 0 to 63\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  64 non-null     object \n",
      " 1   进账        64 non-null     float64\n",
      " 2   进账次数      64 non-null     int64  \n",
      " 3   出账        64 non-null     float64\n",
      " 4   出账次数      64 non-null     int64  \n",
      " 5   查询账号      64 non-null     object \n",
      " 6   进出差       64 non-null     float64\n",
      " 7   进出差次数     64 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 4.1+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13 entries, 0 to 12\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  13 non-null     object \n",
      " 1   进账        13 non-null     float64\n",
      " 2   进账次数      13 non-null     int64  \n",
      " 3   出账        13 non-null     float64\n",
      " 4   出账次数      13 non-null     int64  \n",
      " 5   查询账号      13 non-null     object \n",
      " 6   进出差       13 non-null     float64\n",
      " 7   进出差次数     13 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 964.0+ bytes\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 229 entries, 0 to 228\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  229 non-null    object \n",
      " 1   进账        229 non-null    float64\n",
      " 2   进账次数      229 non-null    int64  \n",
      " 3   出账        229 non-null    float64\n",
      " 4   出账次数      229 non-null    int64  \n",
      " 5   查询账号      229 non-null    object \n",
      " 6   进出差       229 non-null    float64\n",
      " 7   进出差次数     229 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 14.4+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 27 entries, 0 to 26\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  27 non-null     object \n",
      " 1   进账        27 non-null     float64\n",
      " 2   进账次数      27 non-null     int64  \n",
      " 3   出账        27 non-null     float64\n",
      " 4   出账次数      27 non-null     int64  \n",
      " 5   查询账号      27 non-null     object \n",
      " 6   进出差       27 non-null     float64\n",
      " 7   进出差次数     27 non-null     int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 1.8+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 401 entries, 0 to 400\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  401 non-null    object \n",
      " 1   进账        401 non-null    float64\n",
      " 2   进账次数      401 non-null    int64  \n",
      " 3   出账        401 non-null    float64\n",
      " 4   出账次数      401 non-null    int64  \n",
      " 5   查询账号      401 non-null    object \n",
      " 6   进出差       401 non-null    float64\n",
      " 7   进出差次数     401 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 25.2+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 363 entries, 0 to 362\n",
      "Data columns (total 8 columns):\n",
      " #   Column    Non-Null Count  Dtype  \n",
      "---  ------    --------------  -----  \n",
      " 0   收款方的商户名称  363 non-null    object \n",
      " 1   进账        363 non-null    float64\n",
      " 2   进账次数      363 non-null    int64  \n",
      " 3   出账        363 non-null    float64\n",
      " 4   出账次数      363 non-null    int64  \n",
      " 5   查询账号      363 non-null    object \n",
      " 6   进出差       363 non-null    float64\n",
      " 7   进出差次数     363 non-null    int64  \n",
      "dtypes: float64(3), int64(3), object(2)\n",
      "memory usage: 22.8+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "import xmind\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "target_dir = r\"D:\\workplace\\工作任务\\资金流向图\\2024-09-07\\微信资金交易流水-xmind.xlsx\"\n",
    "df = pd.read_excel(target_dir)\n",
    "df_jin = df[df['进出标准'] == '进']\n",
    "df_chu = df[df['进出标准'] == '出']\n",
    "df_group = df.groupby(['查询账号','进出标准','收款方的商户名称']).agg({'交易金额':[np.sum,np.size]}).reset_index()\n",
    "# 重新排列\n",
    "df_group.columns = ['查询账号','进出标准','收款方的商户名称','交易金额','交易次数']\n",
    "#print(df_group.info())\n",
    "# 获取一级主题\n",
    "first_theme = df_group['查询账号'].unique()\n",
    "#print(first_theme)\n",
    "# 获取二级主题\n",
    "workbook = xmind.load(\"my.xmind\")\n",
    "sheet = workbook.getPrimarySheet()\n",
    "sheet.setTitle(\"微信资金流水数据\")\n",
    "root_topic = sheet.getRootTopic()\n",
    "root_topic.setTitle(\"资金流向分析\")\n",
    "for theme_name in first_theme:\n",
    "    df_ximd_tmp = df_group[df_group['查询账号'] == theme_name]\n",
    "    df_ximd_tmp.groupby(['收款方的商户名称','进出标准']).agg(总金额=('交易金额','sum'), 交易次数=('交易次数','sum')).reset_index()\n",
    "    #df_ximd_tmp.to_excel(r'F:\\工作任务\\2024-09-11xmind资金流向图\\微信资金流水数据-分组数据-2.xlsx')\n",
    "    #print(df_ximd_tmp.info())\n",
    "    # 分组和聚合\n",
    "    result = df_ximd_tmp.groupby('收款方的商户名称').agg(\n",
    "        进账=('交易金额',lambda x:x[df['进出标准']==\"进\"].sum()),\n",
    "        进账次数=('交易次数',lambda x:x[df['进出标准']==\"进\"].sum()),\n",
    "        出账=('交易金额',lambda x:x[df['进出标准']==\"出\"].sum()),\n",
    "        出账次数=('交易次数',lambda x:x[df['进出标准']==\"出\"].sum()),\n",
    "        ).reset_index()\n",
    "    result['查询账号'] = theme_name\n",
    "    result['进出差']= result['进账']-result['出账']\n",
    "    result['进出差次数'] = result['进账次数']-result['出账次数']\n",
    "    print(result.info())\n",
    "    # 进账：1372333.04元，160次，出账：1366243.74元，154次，进出差：6089.30元，6次\n",
    "    result['进出说明'] = result.apply(lambda x:f\"{x['收款方的商户名称']}[进账：{x['进账']}，{x['进账次数']}次，出账：{x['出账']}，{x['出账次数']}次，进出差：{x['进出差']}，{x['进出差次数']}次]\",axis=1)\n",
    "    # 设置一级标题\n",
    "    subTopic =TopicElement(ownerWorkbook=workbook)\n",
    "    subTopic.setTitle(theme_name)\n",
    "    #subTopic.setTitle(theme_name)\n",
    "    # subTopic = root_topic.addSubTopic()\n",
    "    # subTopic.setTitle(theme_name)\n",
    "    # 设置二级标题\n",
    "    for index, row in result.iterrows():\n",
    "        #subTopic.addSubTopic().setTitle(row['进出说明'])\n",
    "        subTopic_sub = TopicElement(ownerWorkbook=workbook)\n",
    "        subTopic_sub.setTitle(row['进出说明'])\n",
    "        subTopic.addSubTopic(subTopic_sub)\n",
    "    root_topic.addSubTopic(subTopic)\n",
    "xmind.save(workbook, path=r'D:\\workplace\\工作任务\\资金流向图\\2024-09-07\\test.xmind')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'xmind'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m \u001b[38;5;21;01mxmind\u001b[39;00m  \n\u001b[0;32m      2\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mxmind\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mcore\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m Workbook, TopicElement  \n\u001b[0;32m      4\u001b[0m \u001b[38;5;66;03m# 创建一个新的 XMind 工作簿  \u001b[39;00m\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'xmind'"
     ]
    }
   ],
   "source": [
    "import xmind  \n",
    "from xmind.core import Workbook, TopicElement  \n",
    "\n",
    "# 创建一个新的 XMind 工作簿  \n",
    "workbook = Workbook()  \n",
    "\n",
    "# 创建一个新的思维导图  \n",
    "sheet = workbook.createSheet()  \n",
    "sheet.setTitle(\"示例思维导图\")  \n",
    "\n",
    "# 创建根主题  \n",
    "root_topic = sheet.getRootTopic()  \n",
    "root_topic.setTitle(\"主主题\")  \n",
    "\n",
    "# 添加子主题  \n",
    "sub_topic1 = TopicElement()  \n",
    "sub_topic1.setTitle(\"子主题1\")  \n",
    "root_topic.addSubTopic(sub_topic1)  \n",
    "\n",
    "sub_topic2 = TopicElement()  \n",
    "sub_topic2.setTitle(\"子主题2\")  \n",
    "root_topic.addSubTopic(sub_topic2)  \n",
    "\n",
    "# 添加更多子主题  \n",
    "sub_topic3 = TopicElement()  \n",
    "sub_topic3.setTitle(\"子主题3\")  \n",
    "root_topic.addSubTopic(sub_topic3)  \n",
    "\n",
    "# 保存 XMind 文件  \n",
    "xmind.save(workbook, \"example.xmind\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "excel_file = r\"F:\\工作任务\\2024-09-11xmind资金流向图\\微信资金流水数据-分组数据.xlsx\"\n",
    "df = pd.read_excel(excel_file)\n",
    "print(df.info())\n",
    "for index, row in df.iterrows():\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "import xmind\n",
    "from xmind.core.const import TOPIC_DETACHED\n",
    "from xmind.core.markerref import MarkerId\n",
    "from xmind.core.topic import TopicElement\n",
    "from xmind.core.position import Position \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "target_dir = r\"D:\\workplace\\工作任务\\资金流向图\\2024-09-07\\微信资金交易流水-xmind.xlsx\"\n",
    "df = pd.read_excel(target_dir)\n",
    "df_jin = df[df['进出标准'] == '进']\n",
    "df_chu = df[df['进出标准'] == '出']\n",
    "df_group = df.groupby(['查询账号','进出标准','收款方的商户名称']).agg({'交易金额':[np.sum,np.size]}).reset_index()\n",
    "# 重新排列\n",
    "df_group.columns = ['查询账号','进出标准','收款方的商户名称','交易金额','交易次数']\n",
    "#print(df_group.info())\n",
    "# 获取一级主题\n",
    "first_theme = df_group['查询账号'].unique()\n",
    "#print(first_theme)\n",
    "# 获取二级主题\n",
    "\n",
    "for theme_name in first_theme:\n",
    "    df_ximd_tmp = df_group[df_group['查询账号'] == theme_name]\n",
    "    df_ximd_tmp.groupby(['收款方的商户名称','进出标准']).agg(总金额=('交易金额','sum'), 交易次数=('交易次数','sum')).reset_index()\n",
    "    df_ximd_tmp.to_excel(r'F:\\工作任务\\2024-09-11xmind资金流向图\\微信资金流水数据-分组数据-2.xlsx')\n",
    "    #print(df_ximd_tmp.info())\n",
    "    # 分组和聚合\n",
    "    result = df_ximd_tmp.groupby('收款方的商户名称').agg(\n",
    "        进账=('交易金额',lambda x:x[df['进出标准']==\"进\"].sum()),\n",
    "        进账次数=('交易次数',lambda x:x[df['进出标准']==\"进\"].sum()),\n",
    "        出账=('交易金额',lambda x:x[df['进出标准']==\"出\"].sum()),\n",
    "        出账次数=('交易次数',lambda x:x[df['进出标准']==\"出\"].sum()),\n",
    "        ).reset_index()\n",
    "    result['查询账号'] = theme_name\n",
    "    result['进出差']= result['进账']-result['出账']\n",
    "    result['进出差次数'] = result['进账次数']-result['出账次数']\n",
    "    print(result.info())\n",
    "    # 进账：1372333.04元，160次，出账：1366243.74元，154次，进出差：6089.30元，6次\n",
    "    result['进出说明'] = result.apply(lambda x:f\"{x['收款方的商户名称']}[进账：{x['进账']}，{x['进账次数']}次，出账：{x['出账']}，{x['出账次数']}次，进出差：{x['进出差']}，{x['进出差次数']}次]\",axis=1)\n",
    "    #print(result.info())\n",
    "    result.to_excel(r'D:\\workplace\\工作任务\\资金流向图\\2024-09-07\\微信资金流水数据-分组数据-3.xlsx')\n",
    "    break\n",
    "#df_group.to_excel(r'F:\\工作任务\\2024-09-11xmind资金流向图\\微信资金流水数据-分组数据.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "undefined.undefined.undefined"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
